package com.netcracker.util.dao.impl;

import com.netcracker.db.exception.DBException;
import com.netcracker.db.DBManager;
import com.netcracker.entity.Publisher;
import com.netcracker.entity.record.PublisherRecord;
import com.netcracker.util.dao.DAOFacade;
import com.netcracker.util.dao.PublisherDAO;
import com.netcracker.util.dao.closer.DAOCloseHelper;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class PublisherDAOImpl extends DAOCloseHelper implements PublisherDAO {
    
    private static final String PUBLISHER_COL_PUBLISHERID = "PUBLISHERID";
    private static final String PUBLISHER_COL_PUBLISHERNAME = "PUBLISHERNAME";
    private static final String PUBLISHER_COL_COUNTRYID = "COUNTRYID";
    
    private static final String CREATE_PUBLISHER_RECORD = "INSERT INTO publishers VALUES "
            + "(PublisherSequence.nextval, ?, ?)";
    private static final String UPDATE_PUBLISHER_RECORD = "UPDATE publishers SET publishername = ?, "
            + "countryid = ? WHERE publisherid = ?";
    private static final String REMOVE_PUBLISHER_RECORD = "DELETE FROM publishers WHERE publisherid = ?";
    
    private static final String PUBLISHER_COUNT = "SELECT COUNT(*) FROM publishers";
    private static final String FIND_ALL_PUBLISHERS = "SELECT * FROM publishers";
    private static final String FIND_PUBLISHER_BY_ID = "SELECT * FROM publishers WHERE publisherid = ?";

    private DBManager dbManager;
    private DAOFacade daoFacade;
    
    public PublisherDAOImpl(DBManager dbManager, DAOFacade daoFacade) {
        this.dbManager = dbManager;
        this.daoFacade = daoFacade;
    }
        
    @Override
    public void setDAOFacade(DAOFacade daoFacade) {
        this.daoFacade = daoFacade;
    }
    
    @Override
    public void setDBManager(DBManager dbManager) {
        this.dbManager = dbManager;
    }

    @Override
    public void create(PublisherRecord publisher) throws DBException {
        PreparedStatement preparedStatement = null;
        try {
            dbManager.commit();
            preparedStatement = dbManager.prepareStatement(CREATE_PUBLISHER_RECORD);
            preparedStatement.setString(1, publisher.getName());
            preparedStatement.setInt(2, publisher.getCountryID());
            preparedStatement.executeQuery();
            dbManager.commit();
        } catch(SQLException ex) {
            dbManager.rollback();
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeStatement(preparedStatement);
        }
    }

    @Override
    public void edit(PublisherRecord publisher) throws DBException {
        PreparedStatement preparedStatement = null;
        try {
            dbManager.commit();
            preparedStatement = dbManager.prepareStatement(UPDATE_PUBLISHER_RECORD);
            preparedStatement.setString(1,publisher.getName());
            preparedStatement.setInt(2, publisher.getCountryID());
            preparedStatement.setInt(3, publisher.getId());
            preparedStatement.executeQuery();
            dbManager.commit();
        } catch(SQLException ex) {
            dbManager.rollback();
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeStatement(preparedStatement);
        }
    }

    @Override
    public void remove(int publisherID) throws DBException {
        PreparedStatement preparedStatement = null;
        try {
            dbManager.commit();            
            preparedStatement = dbManager.prepareStatement(REMOVE_PUBLISHER_RECORD);
            preparedStatement.setInt(1, publisherID);
            preparedStatement.executeQuery();
            dbManager.commit();
        } catch (SQLException ex) {
            dbManager.rollback();
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeStatement(preparedStatement);
        }
    }

    @Override
    public Publisher find(int publisherID) throws DBException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = dbManager.prepareStatement(FIND_PUBLISHER_BY_ID);
            preparedStatement.setInt(1,publisherID);
            resultSet = preparedStatement.executeQuery();
            return getPublishers(resultSet).get(0);
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } finally {           
            closeResultSet(resultSet);
            closeStatement(preparedStatement);
        }
    }

    @Override
    public List<Publisher> findAll() throws DBException {
        ResultSet resultSet = null;
        try {
            resultSet = dbManager.execSQL(FIND_ALL_PUBLISHERS);
            return getPublishers(resultSet);
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeResultSet(resultSet);
        }
    }

    @Override
    public int count() throws DBException {
        ResultSet resultSet = null;
        try {
            resultSet = dbManager.execSQL(PUBLISHER_COUNT);
            int count = 0;
            while(resultSet.next()){
                count = resultSet.getInt(1);
            }
            return count;
        } catch(SQLException ex) {
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeResultSet(resultSet);
        }    
    }

    private Publisher getPublisherEntity(ResultSet resultSet) throws SQLException, DBException {
        Publisher publisher = new Publisher();
        publisher.setId(resultSet.getInt(PUBLISHER_COL_PUBLISHERID));
        publisher.setName(resultSet.getString(PUBLISHER_COL_PUBLISHERNAME));
        publisher.setCountry(daoFacade.getCountryDAO().find(resultSet.getInt(PUBLISHER_COL_COUNTRYID)));
        return publisher;
    }

    private List<Publisher> getPublishers(ResultSet resultSet) throws SQLException, DBException {
        List<Publisher> publishers = new ArrayList<>();    
        while(resultSet.next()) {
            publishers.add(getPublisherEntity(resultSet));
        }
        return publishers;
    }
    
}
